package com.zhan.excel_deal.controller;

import com.zhan.excel_deal.entity.Parent;
import com.zhan.excel_deal.util.FileUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.boot.ansi.AnsiOutput;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author zhanyin
 * @since 2021/8/16
 */
@RestController
@SuppressWarnings("all")
public class DealController {

    @RequestMapping(value = {"/excelImport"}, method = {RequestMethod.POST})
    public void excelImport(@RequestParam("files") MultipartFile[] multipartFiles, HttpServletRequest request, String parentParam, String childParams, HttpServletResponse response) throws Exception {

        //读取父表
        File file1 = FileUtil.multipartFileToFile(multipartFiles[0]);
        if (file1.getName().contains("xlsx")) {
            FileInputStream fs1 = new FileInputStream(file1);
            XSSFWorkbook wb1 = new XSSFWorkbook(fs1);
            XSSFSheet sheet1 = wb1.getSheetAt(0);

            //读取子表
            File file2 = FileUtil.multipartFileToFile(multipartFiles[1]);
            FileInputStream fs2 = new FileInputStream(file2);
            XSSFWorkbook wb2 = new XSSFWorkbook(fs2);
            XSSFSheet sheet2 = wb2.getSheetAt(0);

            //子表需要填入信息的字段
            String[] split = parentParam.split(",");
            int length = split.length;

            //父表字段长度
            int colmNum = sheet1.getRow(0).getPhysicalNumberOfCells();

            //用于保存父类数据
            List<Map<String, Object>> parentList = new ArrayList<>();
            Map<String, Object> parentMap = new HashMap<>();

            //获取父表头所需字段序号
            HashMap<String, Integer> parentIndexMap = new HashMap<>();
            for (int i = 0; i < colmNum; i++) {
                for (int j = 0; j < split.length; j++) {
                    if (sheet1.getRow(0).getCell(i).getStringCellValue().equals(split[j])) {
                        parentIndexMap.put(split[j], i);
                        continue;
                    }
                }
                if (sheet1.getRow(0).getCell(i).getStringCellValue().equals(parentParam)) {
                    parentIndexMap.put(parentParam, i);
                }
            }

            //遍历父表获取所需要集合
            int lastRowNum1 = sheet1.getLastRowNum();
            for (int i = 1; i <= lastRowNum1; i++) {
                for (int j = 0; j < parentIndexMap.size(); j++) {
                    for (String s : split) {
                        if (sheet1.getRow(i).getCell(parentIndexMap.get(s)) == null) {
                            sheet1.getRow(i).createCell(parentIndexMap.get(s)).setCellValue("");
                        }
                        parentMap.put(s, sheet1.getRow(i).getCell(parentIndexMap.get(s)));
                    }
                }
                parentList.add(parentMap);
            }


            //获取子表头所需字段序号
            HashMap<String, Integer> childIndexMap = new HashMap<>();
            for (int i = 0; i < colmNum; i++) {
                for (int j = 0; j < split.length; j++) {
                    if (sheet1.getRow(0).getCell(i).getStringCellValue().equals(split[j])) {
                        childIndexMap.put(split[j], i);
                        continue;
                    }
                }
                if (sheet1.getRow(0).getCell(i).getStringCellValue().equals(parentParam)) {
                    childIndexMap.put(parentParam, i);
                }
            }

            //填充数据到子表
            int lastRowNum2 = sheet2.getLastRowNum();
            for (int i = 1; i < lastRowNum2; i++) {
                for (Map<String, Object> parent : parentList) {
                    if (sheet2.getRow(i).getCell(childIndexMap.get(parentParam)).equals(parent.get(parentParam))) {
                        for (String s : split) {
                            if (sheet2.getRow(i).getCell(childIndexMap.get(s)) == null) {
                                sheet2.getRow(i).createCell(childIndexMap.get(s)).setCellValue("");
                            }
                            sheet2.getRow(i).getCell(childIndexMap.get(s)).setCellValue((String) parent.get(s));
                        }

                    }
                }
            }

            ServletOutputStream out = response.getOutputStream();
            FileUtil.delteTempFile(file1);
            FileUtil.delteTempFile(file2);
            out.flush();
            wb2.write(out);
            fs1.close();
            fs2.close();
        }else{
            FileInputStream fs1 = new FileInputStream(file1);
            HSSFWorkbook wb1 = new HSSFWorkbook(fs1);
            HSSFSheet sheet1 = wb1.getSheetAt(0);

            //读取子表
            File file2 = FileUtil.multipartFileToFile(multipartFiles[1]);
            FileInputStream fs2 = new FileInputStream(file2);
            HSSFWorkbook wb2 = new HSSFWorkbook(fs2);
            HSSFSheet sheet2 = wb2.getSheetAt(0);

            //子表需要填入信息的字段
            String[] split = childParams.split(",");
            int length = split.length;

            //父表字段长度
            int colmNum = sheet1.getRow(0).getPhysicalNumberOfCells();

            //用于保存父类数据
            List<Map<String, Object>> parentList = new ArrayList<>();
            Map<String, Object> parentMap = new HashMap<>();

            //获取父表头所需字段序号
            HashMap<String, Integer> parentIndexMap = new HashMap<>();
            for (int i = 0; i <colmNum; i++) {
                for (int j = 0; j < split.length; j++) {
                    if (sheet1.getRow(0).getCell(i).getStringCellValue().equals(split[j])) {
                        parentIndexMap.put(split[j], i);
                    }
                }
                if (sheet1.getRow(0).getCell(i).getStringCellValue().equals(parentParam)) {
                    parentIndexMap.put(parentParam, i);
                }
            }

            //遍历父表获取所需要集合
            int lastRowNum1 = sheet1.getLastRowNum();
            for (int i = 1; i <= lastRowNum1; i++) {
                    for (String s : split) {
                        System.out.println(s);
                        System.out.println(sheet1.getRow(i).getCell(6).getStringCellValue());
                        System.out.println(parentIndexMap.get(s));
                        if (sheet1.getRow(i).getCell(parentIndexMap.get(s))== null) {
                            sheet1.getRow(i).createCell(parentIndexMap.get(s)).setCellValue("");
                        }
                        parentMap.put(s, sheet1.getRow(i).getCell(parentIndexMap.get(s)).getStringCellValue());
                    }
                parentList.add(parentMap);
            }


            //获取子表头所需字段序号
            HashMap<String, Integer> childIndexMap = new HashMap<>();
            for (int i = 0; i < colmNum; i++) {
                for (int j = 0; j < split.length; j++) {
                    if (sheet1.getRow(0).getCell(i).getStringCellValue().equals(split[j])) {
                        childIndexMap.put(split[j], i);
                        continue;
                    }
                }
                if (sheet1.getRow(0).getCell(i).getStringCellValue().equals(parentParam)) {
                    childIndexMap.put(parentParam, i);
                }
            }

            //填充数据到子表
            int lastRowNum2 = sheet2.getLastRowNum();
            for (int i = 1; i < lastRowNum2; i++) {
                for (Map<String, Object> parent : parentList) {
                    if (sheet2.getRow(i).getCell(childIndexMap.get(parentParam)) == null) {
                        sheet2.getRow(i).createCell(childIndexMap.get(parentParam)).setCellValue("");
                    }
                    if (sheet2.getRow(i).getCell(childIndexMap.get(parentParam)).equals(parent.get(parentParam))) {
                        for (String s : split) {
                            if (sheet2.getRow(i).getCell(childIndexMap.get(s)) == null) {
                                sheet2.getRow(i).createCell(childIndexMap.get(s)).setCellValue("");
                            }
                            sheet2.getRow(i).getCell(childIndexMap.get(s)).setCellValue((String) parent.get(s));
                        }

                    }
                }
            }

            ServletOutputStream out = response.getOutputStream();
            FileUtil.delteTempFile(file1);
            FileUtil.delteTempFile(file2);
            out.flush();
            wb2.write(out);
            fs1.close();
            fs2.close();

        }
    }
}
